package com.apes.fn.tms.repository;

import com.apes.fn.tms.model.*;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface WaybillRepository extends CustomRepository<Waybill, String> {

    List<Waybill> findAllByIdAndTransportStatus(String id, String status);

    @Query(value = "SELECT t.* from tms_waybill t where t.transport_status = :status  and rownum<=50 order by t.last_updated_stamp",nativeQuery = true)
    List<Waybill> findAllByTransportStatus(@Param("status")  String status );

    @Query(value = "SELECT * from tms_waybill t where t.transport_status = :status order by t.id ",nativeQuery = true)
    Page<Waybill> findAllByTransportStatusPage(@Param("status")  String status , Pageable pageable);


    @Query(value = "SELECT t.* from tms_waybill t where t.transport_status = :status and t.create_date >= :startValue and t.create_date <= :endValue  order by t.create_date desc",nativeQuery = true)
    List<Waybill> findAllByTransportStatusAndCreateDate(@Param("status")  String status , @Param("startValue") Date startValue, @Param("endValue")Date endValue);

    @Query(value = "SELECT t.* from TMS_WAYBILL_CARGO_DETAILS t where t.waybill_id = :waybillId",nativeQuery = true)
    List<WaybillCargoDetails> findAllByWaybillId(@Param("waybillId") String waybillId);


    @Query(value = "select t.* from tms_waybill t,(select t.phy_Depot_id,t.execution_number from tms_branch_line_item t where t.branch_line_id in( " +
            "select t.branch_line_id from TMS_CAR_INFORMATION t where t.id = :carInfomation) ) l  where t.id in(" +
            "select t.waybill_id from TMS_CAR_DETAILS t where t.car_infomation_id = :carInfomation ) " +
            "and t.transport_status = :staus and t.shipping_address = l.phy_Depot_id " +
            " ORDER BY l.execution_number ASC ",nativeQuery = true)
    List<Waybill> findAllByCarInfomationAndStausAndASC(@Param("carInfomation") String carInfomation,@Param("staus") String staus);

    @Query(value = "select t.* from tms_waybill t,(select t.phy_Depot_id,t.execution_number from tms_branch_line_item t where t.branch_line_id in( " +
            "select t.branch_line_id from TMS_CAR_INFORMATION t where t.id = :carInfomation) ) l  where t.id in(" +
            "select t.waybill_id from TMS_CAR_DETAILS t where t.car_infomation_id = :carInfomation ) " +
            "and t.transport_status = :staus and t.shipping_address = l.phy_Depot_id " +
            " ORDER BY l.execution_number DESC ",nativeQuery = true)
    List<Waybill> findAllByCarInfomationAndStausAndDESC(@Param("carInfomation") String carInfomation,@Param("staus") String staus);

    List<Waybill> findBySourceBusinessKeyAndSourceBusinessType(String sourceBusinessKey,String sourceBusinessType);


    @Query(value = "select t.* from tms_waybill t,(select t.phy_Depot_id,t.execution_number from tms_branch_line_item t where t.branch_line_id in( " +
            "select t.branch_line_id from TMS_CAR_INFORMATION t where t.id = :carInfomation) ) l  where t.id in(" +
            "select t.waybill_id from TMS_CAR_DETAILS t where t.car_infomation_id = :carInfomation ) " +
            "and t.shipping_address = l.phy_Depot_id " +
            " ORDER BY l.execution_number ASC ",nativeQuery = true)
    List<Waybill> findAllByCarInfomation(@Param("carInfomation") String carInfomation);

    /**
     * 未装货
     * @return
     */
    @Query(value = "SELECT A.* FROM TMS_WAYBILL A WHERE source_business_key = :source_business_key AND outbound_Order IS NULL AND transport_status IN ('collection', 'sendCar', 'loading')", nativeQuery = true)
    Waybill findUnload(@Param("source_business_key")String source_business_key);

    @Query(value = "SELECT t.* from tms_waybill t where t.transport_status = :status " +
            "and t.create_date >= :startValue and t.create_date <= :endValue " +
            "and t.aims_key like %:purchaseOrder% and t.supplier_id in " +
            "(SELECT r.id FROM mas_party_role r WHERE r.party_id " +
            "in (SELECT p.id FROM mas_party p WHERE p.name like  %:supplier_id%)" +
            "and r.role_id = 'Supplier' ) order by t.create_date desc",nativeQuery = true)
    List<Waybill> findAllByTransportStatusAndPurchaseOrderAndSupplier(@Param("status")  String status, @Param("purchaseOrder") String purchaseOrder,
                                                                      @Param("supplier_id") String supplier_id, @Param("startValue") Date startValue, @Param("endValue")Date endValue);

    @Query(value = "SELECT t.* from tms_waybill t where t.transport_status = :status and t.aims_key like %:purchaseOrder% " +
            "and t.create_date >= :startValue and t.create_date <= :endValue  order by t.create_date desc",nativeQuery = true)
    List<Waybill> findAllByTransportStatusAndPurchaseOrder(@Param("status")  String status,@Param("purchaseOrder") String purchaseOrder,
                                                           @Param("startValue") Date startValue, @Param("endValue")Date endValue);

    @Query(value = "SELECT t.* from tms_waybill t where t.transport_status = :status " +
            "and t.create_date >= :startValue and t.create_date <= :endValue and t.supplier_id in " +
            "(SELECT r.id FROM mas_party_role r WHERE r.party_id " +
            "in (SELECT p.id FROM mas_party p WHERE p.name like  %:supplier_id%) and r.role_id = 'Supplier' )" +
            " order by t.create_date desc",nativeQuery = true)
    List<Waybill> findAllByTransportStatusAndSupplier(@Param("status")  String status,@Param("supplier_id") String supplier_id,
                                                      @Param("startValue") Date startValue, @Param("endValue")Date endValue);
}
